#单表信息操作类
import pymysql
from mydemo import config
class model:
	table_name=None#表名
	link=None#数据库连接对象
	cursor=None#游标对象
	pk='id'#表的主键名
	def __init__(self,table,config=config):
		'''构造方法，初始化表名，连接数据库'''
		try:
			self.table_name=table
			self.link=pymysql.connect(host=config.HOST,user=config.USER,password=config.PASSWD,db=config.DEMONAME,port=config.PORT,charset='utf8')
			self.cursor=self.link.cursor(pymysql.cursors.DictCursor)         
			print("数据库连接操作成功")
		except Exception as err:
			print("数据库连接操作失败\n错误原因：%s"%err)
	def __loadFields(self):
		'''加载当前表的字段信息，内部私有方法'''
		sql="SHOW COLUMNS FROM %s"%(self.table_name)
		self.cursor.execute(sql)
		dlist=self.cursor.fetchall()
		#遍历所有表信息
		for t in dlist:
			self.fileds.append(t['fileds'])
			if t['key']=='PRI':
				self.pk=t['fileds']
	def findall(self):
		'''获取指定表格的全部信息，没有返回[] '''
		try:
			sql="select * from %s"%(self.table_name)
			print(sql)
			self.cursor.execute(sql)
			return self.cursor.fetchall()
		except Exception as err:
			print("数据库全部数据查询操作失败\n错误原因：%s"%err)
			return []
	def findone(self,id=0):
		'''获取指定id的单条信息，没有返回None '''
		try:
			sql="select * from %s where %s=%s"%(self.table_name,self.pk,id)
			self.cursor.execute(sql)
			return self.cursor.fetchone()
		except Exception as err:
			print("数据库单条数据查询操作失败\n错误原因：%s"%err)
			return None
	def select(self,where=[],order=None,limit=None):
		'''获取特定条件的表格信息，没有返回[] '''
		try:
			sql="select * from %s"%(self.table_name)
			if isinstance(where,list) and len(where)>0:
				sql+=" where "+" and ".join(where)
			if 	order is not None:
				sql+=" order by "+order
			if 	limit is not None:
				sql+=" limit "+str(limit)
			print(sql)
			self.cursor.execute(sql)
			return self.cursor.fetchall()
		except Exception as err:
			print("数据库特定数据查询操作失败\n错误原因：%s"%err)
			return []		
	def save(self,data={}):
		'''信息添加'''
		try:
			#组装sql语句	
			keys=[]
			values=[]
			for k,v in data.items():
				keys.append(k)
				values.append(v)
			sql="insert into %s(%s) values(%s)"%(self.table_name,','.join(keys),','.join(["%s"]*len(values)))
			print(sql)
			self.cursor.execute(sql,tuple(values))
			self.link.commit()
			return self.cursor.lastrowid
		except Exception as err:
			print("数据库数据增加操作失败\n错误原因：%s"%err)
			return 0
	def update(self,data={}):
		try:
			values=[]#值列表
			for k,v in data.items():
				if k!=self.pk:
					values.append("%s='%s'"%(k,v))
			sql="update %s set %s where %s='%s'"%(self.table_name,','.join(values),self.pk,data.get(self.pk))
			print(sql)
			self.cursor.execute(sql)
			self.link.commit()
			return self.cursor.rowcount
		except Exception as err:
			print("数据库数据修改操作失败\n错误原因：%s"%err)
			return 0		
	def delete(self,id=0):
		try:
			sql="delete from %s where %s='%s'"%(self.table_name,self.pk,id)
			print(sql)
			self.cursor.execute(sql)
			self.link.commit()
			return self.cursor.rowcount
		except Exception as err:
			print("数据库数据删除操作失败\n错误原因：%s"%err)
			return 0	
	def count(self):   
		try:
			sql = "select count(%s) as num from %s"%(self.pk,self.table_name) 
			self.cursor.execute(sql)
			return self.cursor.fetchone()  
		except Exception as err:
			print("SQL数据统计错误，原因：%s"%err)
			return None	
	def countnum(self,count):
		key=0
		for k,v in count.items():
			if k!=None:
				key=v
		return key
	def __del__(self):
		'''关闭游标对象和连接对象'''
		if self.cursor!=None:
			self.cursor.close()
		if self.link!=None:
			self.link.close()